#pragma once

#include <cppconn/prepared_statement.h>
#include <cppconn/resultset.h>
#include "../util/config.hh"
#include "../util/dbconn.hh"
#include "../poco/vocabulary.hh"
#include "../poco/word.hh"

namespace WordMemory
{
    class VocabularyRepository
    {
    public:
        static VocabularyRepository& instance()
        {
            static VocabularyRepository inst;
            return inst;
        }

    public:
        // 获取用户的所有词库
        std::vector<Vocabulary> getVocabularies(int user_id)
        {
            ConnectionGuard conn;
            try
            {
                // 确保该用户有收藏词库, 如果没有会自动创建
                ensureFavoritesVocabularyExists(user_id);

                std::unique_ptr<sql::PreparedStatement> pstmt(
                    conn.get()->prepareStatement(
                        "SELECT v.id, v.user_id, v.name, v.is_default, v.is_favorites, v.is_mistakes_book, "
                        "(SELECT COUNT(*) FROM vocabulary_words vw WHERE vw.vocabulary_id = v.id) AS word_count "
                        "FROM vocabularies v "
                        "WHERE v.user_id = ? "
                        "ORDER BY v.is_favorites ASC, v.id ASC")); // 收藏词库通常放前面或后面
                pstmt->setInt(1, user_id);
                std::unique_ptr<sql::ResultSet> res(pstmt->executeQuery());

                std::vector<Vocabulary> vocabularies;
                while (res->next())
                {
                    vocabularies.emplace_back(
                        Vocabulary(
                            res->getInt("id"),
                            res->getInt("user_id"),
                            res->getString("name"),
                            res->getInt("word_count"),
                            res->getBoolean("is_default"),
                            res->getBoolean("is_favorites"),
                            res->getBoolean("is_mistakes_book")
                        ));
                }
                return vocabularies;
            }
            catch (const sql::SQLException& e)
            {
                spdlog::error("Failed to get vocabularies for user_id {}: {}", user_id, e.what());
                throw RepositoryExcept("Failed to get vocabularies: " + std::string(e.what()));
            }
        }

        // 添加普通词库
        Vocabulary addVocabulary(int user_id, const std::string& name)
        {
            ConnectionGuard conn;
            try
            {
                std::unique_ptr<sql::PreparedStatement> pstmt(
                    conn.get()->prepareStatement(
                        "INSERT INTO vocabularies (user_id, name, is_favorites, is_default) VALUES (?, ?, FALSE, FALSE)"));
                pstmt->setInt(1, user_id);
                pstmt->setString(2, name);
                pstmt->executeUpdate();

                int new_id = getLastInsertId(conn);
                return getVocabularyById(new_id, conn);
            }
            catch (const sql::SQLException& e)
            {
                spdlog::error("Failed to create vocabulary '{}' for user_id {}: {}", name, user_id, e.what());
                throw RepositoryExcept("Failed to create vocabulary: " + std::string(e.what()));
            }
        }

        // 获取或创建用户的收藏词库
        Vocabulary getOrCreateFavoritesVocabulary(int user_id)
        {
            ConnectionGuard conn;
            try
            {
                std::unique_ptr<sql::PreparedStatement> pstmt_select(
                    conn.get()->prepareStatement(
                        "SELECT v.id, v.user_id, v.name, v.is_default, v.is_favorites, v.is_mistakes_book, "
                        "(SELECT COUNT(*) FROM vocabulary_words vw WHERE vw.vocabulary_id = v.id) AS word_count "
                        "FROM vocabularies v "
                        "WHERE v.user_id = ? AND v.is_favorites = TRUE LIMIT 1"));
                pstmt_select->setInt(1, user_id);
                std::unique_ptr<sql::ResultSet> res(pstmt_select->executeQuery());

                if (res->next())
                {
                    return Vocabulary(
                        res->getInt("id"),
                        res->getInt("user_id"),
                        res->getString("name"),
                        res->getInt("word_count"),
                        res->getBoolean("is_default"),
                        res->getBoolean("is_favorites"),
                        res->getBoolean("is_mistakes_book")
                    );
                }
                else
                {
                    // 创建收藏词库
                    std::unique_ptr<sql::PreparedStatement> pstmt_insert(
                        conn.get()->prepareStatement(
                            "INSERT INTO vocabularies (user_id, name, is_favorites, is_default) VALUES (?, '我的收藏', TRUE, FALSE)"));
                    pstmt_insert->setInt(1, user_id);
                    pstmt_insert->executeUpdate();
                    int new_id = getLastInsertId(conn);
                    return getVocabularyById(new_id, conn);
                }
            }
            catch (const sql::SQLException& e)
            {
                spdlog::error("Failed to get or create favorites vocabulary for user_id {}: {}", user_id, e.what());
                throw RepositoryExcept("Failed to get or create favorites vocabulary: " + std::string(e.what()));
            }
        }

        Vocabulary getOrCreateMistakesBook(int user_id)
        {
            ConnectionGuard conn;
            try
            {
                std::unique_ptr<sql::PreparedStatement> pstmt_select(
                    conn.get()->prepareStatement(
                        "SELECT v.id, v.user_id, v.name, v.is_default, v.is_favorites, v.is_mistakes_book, "
                        "(SELECT COUNT(*) FROM vocabulary_words vw WHERE vw.vocabulary_id = v.id) AS word_count "
                        "FROM vocabularies v "
                        "WHERE v.user_id = ? AND v.is_mistakes_book = TRUE LIMIT 1"));
                pstmt_select->setInt(1, user_id);
                std::unique_ptr<sql::ResultSet> res(pstmt_select->executeQuery());

                if (res->next())
                {
                    return Vocabulary(
                        res->getInt("id"),
                        res->getInt("user_id"),
                        res->getString("name"),
                        res->getInt("word_count"),
                        res->getBoolean("is_default"),
                        res->getBoolean("is_favorites"),
                        res->getBoolean("is_mistakes_book")
                    );
                }
                else
                {
                    std::unique_ptr<sql::PreparedStatement> pstmt_insert(
                        conn.get()->prepareStatement(
                            "INSERT INTO vocabularies (user_id, name, is_default, is_favorites, is_mistakes_book) VALUES (?, '我的错题本', FALSE, FALSE, TRUE)"));
                    pstmt_insert->setInt(1, user_id);
                    pstmt_insert->executeUpdate();
                    int new_id = getLastInsertId(conn);
                    return getVocabularyById(new_id, conn);
                }
            }
            catch (const sql::SQLException& e)
            {
                spdlog::error("Failed to get or create mistakes book for user_id {}: {}", user_id, e.what());
                throw RepositoryExcept("Failed to get or create mistakes book: " + std::string(e.what()));
            }
        }

        // 将单词添加到指定词库
        void addWordToVocabulary(int vocabulary_id, int word_id)
        {
            ConnectionGuard conn;
            try
            {
                // 首先检查单词是否已在词库中，避免重复插入导致错误（如果表有唯一约束）
                std::unique_ptr<sql::PreparedStatement> check_stmt(
                    conn.get()->prepareStatement("SELECT id FROM vocabulary_words WHERE vocabulary_id = ? AND word_id = ?"));
                check_stmt->setInt(1, vocabulary_id);
                check_stmt->setInt(2, word_id);
                std::unique_ptr<sql::ResultSet> res(check_stmt->executeQuery());
                if (res->next()) {
                    spdlog::info("Word {} already in vocabulary {}", word_id, vocabulary_id);
                    return; // 已经存在
                }


                std::unique_ptr<sql::PreparedStatement> pstmt(
                    conn.get()->prepareStatement(
                        "INSERT INTO vocabulary_words (vocabulary_id, word_id) VALUES (?, ?)"));
                pstmt->setInt(1, vocabulary_id);
                pstmt->setInt(2, word_id);
                pstmt->executeUpdate();
                spdlog::info("Added word_id {} to vocabulary_id {}", word_id, vocabulary_id);
            }
            catch (const sql::SQLException& e)
            {
                // 1062: Duplicate entry for key 'vocabulary_id_word_id_unique' (or similar unique constraint error)
                if (e.getErrorCode() == 1062) {
                     spdlog::warn("Attempted to add duplicate word_id {} to vocabulary_id {}: {}", word_id, vocabulary_id, e.what());
                     //可以选择忽略或者向上抛出特定异常
                     return; //视为已添加
                }
                spdlog::error("Failed to add word_id {} to vocabulary_id {}: {}", word_id, vocabulary_id, e.what());
                throw RepositoryExcept("Failed to add word to vocabulary: " + std::string(e.what()));
            }
        }

        // 从指定词库移除单词
        void removeWordFromVocabulary(int vocabulary_id, int word_id)
        {
            ConnectionGuard conn;
            try
            {
                std::unique_ptr<sql::PreparedStatement> pstmt(
                    conn.get()->prepareStatement(
                        "DELETE FROM vocabulary_words WHERE vocabulary_id = ? AND word_id = ?"));
                pstmt->setInt(1, vocabulary_id);
                pstmt->setInt(2, word_id);
                pstmt->executeUpdate();
                spdlog::info("Removed word_id {} from vocabulary_id {}", word_id, vocabulary_id);
            }
            catch (const sql::SQLException& e)
            {
                spdlog::error("Failed to remove word_id {} from vocabulary_id {}: {}", word_id, vocabulary_id, e.what());
                throw RepositoryExcept("Failed to remove word from vocabulary: " + std::string(e.what()));
            }
        }

        bool isWordInVocabulary(int vocabulary_id, int word_id) {
            ConnectionGuard conn;
            try {
                std::unique_ptr<sql::PreparedStatement> pstmt(
                    conn.get()->prepareStatement(
                        "SELECT 1 FROM vocabulary_words WHERE vocabulary_id = ? AND word_id = ? LIMIT 1"));
                pstmt->setInt(1, vocabulary_id);
                pstmt->setInt(2, word_id);
                std::unique_ptr<sql::ResultSet> res(pstmt->executeQuery());
                return res->next();
            } catch (const sql::SQLException& e) {
                spdlog::error("Failed to check if word {} is in vocabulary {}: {}", word_id, vocabulary_id, e.what());
                return false;
            }
        }

        // 获取某个词库中的所有单词文本，只包含基本信息，不含例句和助记
        std::vector<Word> getWordsOnlyTextByVocabularyId(int vocabulary_id)
        {
            ConnectionGuard conn;
            try
            {
                std::unique_ptr<sql::PreparedStatement> pstmt(
                    conn.get()->prepareStatement(
                        "SELECT w.id, w.word, w.meaning " // 简化版 Word，不在此处查完整信息
                        "FROM words w "
                        "JOIN vocabulary_words vw ON w.id = vw.word_id "
                        "WHERE vw.vocabulary_id = ?"));
                pstmt->setInt(1, vocabulary_id);
                std::unique_ptr<sql::ResultSet> res(pstmt->executeQuery());

                std::vector<Word> words;
                while (res->next())
                {
                    words.emplace_back(
                        Word(
                            res->getInt("id"),
                            res->getString("word"),
                            res->getString("meaning")
                            // examples 和 mnemonic 留空，由客户端单独获取
                        )
                    );
                }
                return words;
            }
            catch (const sql::SQLException& e)
            {
                spdlog::error("Failed to get words for vocabulary_id {}: {}", vocabulary_id, e.what());
                throw RepositoryExcept("Failed to get vocabulary words: " + std::string(e.what()));
            }
        }

        Vocabulary getDefaultVocabulary(int user_id)
        {
            ConnectionGuard conn;
            try
            {
                std::unique_ptr<sql::PreparedStatement> pstmt(
                    conn.get()->prepareStatement(
                        "SELECT v.id, v.user_id, v.name, v.is_default, v.is_favorites, "
                        "(SELECT COUNT(*) FROM vocabulary_words vw WHERE vw.vocabulary_id = v.id) AS word_count "
                        "FROM vocabularies v "
                        "WHERE v.user_id = ? AND v.is_default = TRUE AND v.is_favorites = FALSE LIMIT 1")); // Default cannot be favorites
                pstmt->setInt(1, user_id);
                std::unique_ptr<sql::ResultSet> res(pstmt->executeQuery());

                if (res->next())
                {
                    return Vocabulary(
                        res->getInt("id"),
                        res->getInt("user_id"),
                        res->getString("name"),
                        res->getInt("word_count"),
                        res->getBoolean("is_default"),
                        res->getBoolean("is_favorites")
                    );
                }
                else
                {
                    spdlog::warn("No default (non-favorites) vocabulary found for user_id {}", user_id);
                    throw RepositoryExcept("Default vocabulary not found"); // Or return an empty/default Vocabulary object
                }
            }
            catch (const sql::SQLException& e)
            {
                spdlog::error("Failed to get default vocabulary for user_id {}: {}", user_id, e.what());
                throw RepositoryExcept("Failed to get default vocabulary: " + std::string(e.what()));
            }
        }

        void setDefaultVocabulary(int user_id, int vocabulary_id)
        {
            ConnectionGuard conn;
            try
            {
                conn.get()->setAutoCommit(false);

                // 将用户的所有非收藏词库设置为非默认
                std::unique_ptr<sql::PreparedStatement> reset_stmt(
                    conn.get()->prepareStatement(
                        "UPDATE vocabularies SET is_default = FALSE WHERE user_id = ? AND is_favorites = FALSE"));
                reset_stmt->setInt(1, user_id);
                reset_stmt->executeUpdate();

                // 设置指定词库为默认 (确保它不是收藏词库)
                std::unique_ptr<sql::PreparedStatement> set_stmt(
                    conn.get()->prepareStatement(
                        "UPDATE vocabularies SET is_default = TRUE WHERE id = ? AND user_id = ? AND is_favorites = FALSE"));
                set_stmt->setInt(1, vocabulary_id);
                set_stmt->setInt(2, user_id);
                int affected_rows = set_stmt->executeUpdate();

                if (affected_rows == 0)
                {
                    conn.get()->rollback();
                    conn.get()->setAutoCommit(true); // Restore autocommit
                    spdlog::warn("Failed to set default vocabulary: id {} not found for user {} or it is a favorites vocabulary.", vocabulary_id, user_id);
                    throw RepositoryExcept("Vocabulary not found, does not belong to user, or is a favorites vocabulary.");
                }

                conn.get()->commit();
                conn.get()->setAutoCommit(true); // Restore autocommit
                spdlog::info("Set default vocabulary to {} for user_id {}", vocabulary_id, user_id);
            }
            catch (const sql::SQLException& e)
            {
                // Ensure rollback and autocommit restoration in case of exception
                try {
                    if (conn.get() && !(conn.get()->getAutoCommit()) ) { // Check if connection is valid and in transaction
                    conn.get()->rollback();
                    }
                } catch (const sql::SQLException& rbEx) {
                    spdlog::error("Rollback failed: {}", rbEx.what());
                }
                try {
                    if (conn.get()) conn.get()->setAutoCommit(true);
                } catch (const sql::SQLException& acEx) {
                    spdlog::error("SetAutoCommit(true) failed: {}", acEx.what());
                }
                spdlog::error("Failed to set default vocabulary: {}", e.what());
                throw RepositoryExcept("Failed to set default vocabulary: " + std::string(e.what()));
            }
        }

    private:
        void ensureFavoritesVocabularyExists(int user_id) { getOrCreateFavoritesVocabulary(user_id); }

        void ensureMistakesBookExists(int user_id) { getOrCreateMistakesBook(user_id); }

        int getLastInsertId(const ConnectionGuard& conn) {
            std::unique_ptr<sql::PreparedStatement> id_stmt(conn.get()->prepareStatement("SELECT LAST_INSERT_ID() AS id"));
            std::unique_ptr<sql::ResultSet> id_res(id_stmt->executeQuery());
            if (id_res->next()) {
                return id_res->getInt("id");
            }
            throw RepositoryExcept("Failed to retrieve last insert ID");
        }

        Vocabulary getVocabularyById(int vocabulary_id, const ConnectionGuard& conn)
        {
            std::unique_ptr<sql::PreparedStatement> select_stmt(
                conn.get()->prepareStatement(
                    "SELECT v.id, v.user_id, v.name, v.is_default, v.is_favorites, v.is_mistakes_book, "
                    "(SELECT COUNT(*) FROM vocabulary_words vw WHERE vw.vocabulary_id = v.id) AS word_count "
                    "FROM vocabularies v WHERE v.id = ?"));
            select_stmt->setInt(1, vocabulary_id);
            std::unique_ptr<sql::ResultSet> res(select_stmt->executeQuery());

            if (res->next())
            {
                return Vocabulary(
                    res->getInt("id"),
                    res->getInt("user_id"),
                    res->getString("name"),
                    res->getInt("word_count"),
                    res->getBoolean("is_default"),
                    res->getBoolean("is_favorites"),
                    res->getBoolean("is_mistakes_book")
                );
            }
            else
            {
                throw RepositoryExcept("Vocabulary not found with id: " + std::to_string(vocabulary_id));
            }
        }

    private:
        VocabularyRepository() = default;
        VocabularyRepository(const VocabularyRepository&) = delete;
        VocabularyRepository& operator=(const VocabularyRepository&) = delete;
    };
}